insert overwrite table jms_dm.dm_contract_area_bill_cnt_dt
select 
 main.date_time 
,main.staff_code   --业务员code
,main.staff_name   --业务员name
,main.network_code --网点code
,main.network_name --网点name
,main.belong_type  --所属类别
,main.up2_bill_count --t-2日发货件量
,main.up1_bill_count --t-2发货件量
,main.total_bill_count  --当日件量
,main.total_bill_mm --当月件量
,main.total_days_mm --当月发货天数
,main.source1_bill_cnt   --桃花岛件量
,main.source2_bill_cnt   --紫金山件量
,main.source3_bill_cnt   --逍遥峰件量
,main.source4_bill_cnt   --七星潭件量
,main.source5_bill_cnt   --极地湾件量
,main.source6_bill_cnt   --其他件量
,main.a_bill_cnt  --A等级客户件量
,main.b2_bill_cnt --B+等级客户件量
,main.b_bill_cnt  --B等级客户件量
,main.c_bill_cnt  --C等级客户件量
,main.d_bill_cnt  --D等级客户件量
,main.e_bill_cnt  --E等级客户件量
,main.f_bill_cnt  --F等级客户件量
,main.cnt_type
,net.agent_code  --网点所属代理区code
,net.agent_name  --网点所属代理区name
,net.fran_code   --网点所属加盟商code
,net.fran_name   --网点所属加盟商name
,main.total_weight_count  --当日总件量
,main.total_weight_mm --当月总件量
,main.date_time as dt
from 
(
select 
    date_time 
    ,staff_code   --业务员code
    ,staff_name   --业务员name
    ,network_code --网点code
    ,network_name --网点name
    ,belong_type  --所属类别
    ,lag(total_bill_count,2,0) over(partition by staff_code,network_code order by date_time) as up2_bill_count --t-2日发货件量
    ,lag(total_bill_count,1,0) over(partition by staff_code,network_code order by date_time) as up1_bill_count --t-2发货件量
    ,total_bill_count  --当日件量
    ,total_bill_mm
    ,count(if(total_bill_count>0,date_time,null)) over(partition by belong_type,network_code,staff_code,date_format(date_time,'yyyy-MM') order by date_time) as total_days_mm --当月发货天数
    ,source1_bill_cnt   --桃花岛件量
    ,source2_bill_cnt   --紫金山件量
    ,source3_bill_cnt   --逍遥峰件量
    ,source4_bill_cnt   --七星潭件量
    ,source5_bill_cnt   --极地湾件量
    ,source6_bill_cnt   --其他件量
    ,a_bill_cnt  --A等级客户件量
    ,b2_bill_cnt --B+等级客户件量
    ,b_bill_cnt  --B等级客户件量
    ,c_bill_cnt  --C等级客户件量
    ,d_bill_cnt  --D等级客户件量
    ,e_bill_cnt  --E等级客户件量
    ,f_bill_cnt  --F等级客户件量
    ,total_weight_count
    ,total_weight_mm
    ,1 as cnt_type
from 
(
select 
date_time 
,staff_code   --业务员code
,staff_name   --业务员name
,network_code --网点code
,network_name --网点name
,belong_type  
,sum(total_bill_count) as total_bill_count
,sum(total_bill_mm) as total_bill_mm
,sum(case when source_id ='326' then total_bill_mm else 0 end) as source1_bill_cnt
,sum(case when source_id ='348' then total_bill_mm else 0 end) as source2_bill_cnt
,sum(case when source_id ='352' then total_bill_mm else 0 end) as source3_bill_cnt
,sum(case when source_id ='353' then total_bill_mm else 0 end) as source4_bill_cnt
,sum(case when source_id ='328' then total_bill_mm else 0 end) as source5_bill_cnt
,sum(case when source_id ='其他' then total_bill_mm else 0 end) as source6_bill_cnt
,sum(case when customer_level ='A' then total_bill_mm else 0  end) as a_bill_cnt
,sum(case when customer_level ='B+' then total_bill_mm else 0 end) as b2_bill_cnt
,sum(case when customer_level ='B' then total_bill_mm else 0 end)  as b_bill_cnt
,sum(case when customer_level ='C' then total_bill_mm else 0 end)  as c_bill_cnt
,sum(case when customer_level ='D' then total_bill_mm else 0 end)  as d_bill_cnt
,sum(case when customer_level ='E' then total_bill_mm else 0 end)  as e_bill_cnt
,sum(case when customer_level ='F' then total_bill_mm else 0 end)  as f_bill_cnt
,sum(total_weight_count) as total_weight_count
,sum(total_weight_mm) as total_weight_mm
from jms_dm.dm_contract_area_bill_customer_detail_dt
where dt between date_add('{{ execution_date  | cst_ds }}',-60) and '{{ execution_date  | cst_ds }}'
group by  staff_code --业务员code
    ,staff_name   --业务员name
    ,network_code --网点code
    ,network_name --网点name
    ,belong_type 
    ,date_time    
)t1
/* --后端不用这些统计
union all 

select 
    date_time 
    ,null as staff_code   --业务员code
    ,null as staff_name   --业务员name
    ,null as network_code --网点code
    ,null as network_name --网点name
    ,belong_type  --所属类别
    ,lag(total_bill_count,2,0) over(partition by belong_type order by date_time) as up2_bill_count --t-2日发货件量
    ,lag(total_bill_count,1,0) over(partition by belong_type order by date_time) as up1_bill_count --t-2发货件量
    ,total_bill_count  --当日件量    
    ,total_bill_mm
    --,sum(total_bill_count) over(partition by belong_type,date_format(date_time,'yyyy-MM') order by date_time) as total_bill_mm --当月件量
    ,count(if(total_bill_count>0,date_time,null)) over(partition by belong_type,date_format(date_time,'yyyy-MM') order by date_time) as total_days_mm --当月发货天数
    ,source1_bill_cnt   --桃花岛件量
    ,source2_bill_cnt   --紫金山件量
    ,source3_bill_cnt   --逍遥峰件量
    ,source4_bill_cnt   --七星潭件量
    ,source5_bill_cnt   --极地湾件量
    ,source6_bill_cnt   --其他件量
    ,a_bill_cnt  --A等级客户件量
    ,b2_bill_cnt --B+等级客户件量
    ,b_bill_cnt  --B等级客户件量
    ,c_bill_cnt  --C等级客户件量
    ,d_bill_cnt  --D等级客户件量
    ,e_bill_cnt  --E等级客户件量
    ,f_bill_cnt  --F等级客户件量
    ,2 as cnt_type
from 
(
select 
date_time 
,belong_type  
,sum(total_bill_count) as total_bill_count
,sum(total_bill_mm) as total_bill_mm
,sum(case when source_id ='326' then total_bill_mm else 0 end) as source1_bill_cnt
,sum(case when source_id ='348' then total_bill_mm else 0 end) as source2_bill_cnt
,sum(case when source_id ='352' then total_bill_mm else 0 end) as source3_bill_cnt
,sum(case when source_id ='353' then total_bill_mm else 0 end) as source4_bill_cnt
,sum(case when source_id ='328' then total_bill_mm else 0 end) as source5_bill_cnt
,sum(case when source_id ='其他' then total_bill_mm else 0 end) as source6_bill_cnt
,sum(case when customer_level ='A' then total_bill_mm else 0  end) as a_bill_cnt
,sum(case when customer_level ='B+' then total_bill_mm else 0 end) as b2_bill_cnt
,sum(case when customer_level ='B' then total_bill_mm else 0 end)  as b_bill_cnt
,sum(case when customer_level ='C' then total_bill_mm else 0 end)  as c_bill_cnt
,sum(case when customer_level ='D' then total_bill_mm else 0 end)  as d_bill_cnt
,sum(case when customer_level ='E' then total_bill_mm else 0 end)  as e_bill_cnt
,sum(case when customer_level ='F' then total_bill_mm else 0 end)  as f_bill_cnt
from jms_dm.dm_contract_area_bill_customer_detail_dt
where dt between date_add('{{ execution_date  | cst_ds }}',-60) and '{{ execution_date  | cst_ds }}'
group by 
     belong_type 
    ,date_time    
)t1

union all 

select 
    date_time 
    ,null staff_code   --业务员code
    ,null staff_name   --业务员name
    ,network_code --网点code
    ,network_name --网点name
    ,belong_type  --所属类别
    ,lag(total_bill_count,2,0) over(partition by belong_type,network_code order by date_time) as up2_bill_count --t-2日发货件量
    ,lag(total_bill_count,1,0) over(partition by belong_type,network_code order by date_time) as up1_bill_count --t-2发货件量
    ,total_bill_count  --当日件量
    ,total_bill_mm
    --,sum(total_bill_count) over(partition by belong_type,network_code,date_format(date_time,'yyyy-MM') order by date_time ) as total_bill_mm --当月件量
    ,count(if(total_bill_count>0,date_time,null)) over(partition by belong_type,network_code,date_format(date_time,'yyyy-MM') order by date_time ) as total_days_mm --当月发货天数
    ,source1_bill_cnt   --桃花岛件量
    ,source2_bill_cnt   --紫金山件量
    ,source3_bill_cnt   --逍遥峰件量
    ,source4_bill_cnt   --七星潭件量
    ,source5_bill_cnt   --极地湾件量
    ,source6_bill_cnt   --其他件量
    ,a_bill_cnt  --A等级客户件量
    ,b2_bill_cnt --B+等级客户件量
    ,b_bill_cnt  --B等级客户件量
    ,c_bill_cnt  --C等级客户件量
    ,d_bill_cnt  --D等级客户件量
    ,e_bill_cnt  --E等级客户件量
    ,f_bill_cnt  --F等级客户件量
    ,3 as cnt_type
from 
(
select 
date_time 
,network_code --网点code
,max(network_name) as network_name --网点name
,belong_type  
,sum(total_bill_count) as total_bill_count
,sum(total_bill_mm) as total_bill_mm
,sum(case when source_id ='326' then total_bill_mm else 0 end) as source1_bill_cnt
,sum(case when source_id ='348' then total_bill_mm else 0 end) as source2_bill_cnt
,sum(case when source_id ='352' then total_bill_mm else 0 end) as source3_bill_cnt
,sum(case when source_id ='353' then total_bill_mm else 0 end) as source4_bill_cnt
,sum(case when source_id ='328' then total_bill_mm else 0 end) as source5_bill_cnt
,sum(case when source_id ='其他' then total_bill_mm else 0 end) as source6_bill_cnt
,sum(case when customer_level ='A' then total_bill_mm else 0  end) as a_bill_cnt
,sum(case when customer_level ='B+' then total_bill_mm else 0 end) as b2_bill_cnt
,sum(case when customer_level ='B' then total_bill_mm else 0 end)  as b_bill_cnt
,sum(case when customer_level ='C' then total_bill_mm else 0 end)  as c_bill_cnt
,sum(case when customer_level ='D' then total_bill_mm else 0 end)  as d_bill_cnt
,sum(case when customer_level ='E' then total_bill_mm else 0 end)  as e_bill_cnt
,sum(case when customer_level ='F' then total_bill_mm else 0 end)  as f_bill_cnt
from jms_dm.dm_contract_area_bill_customer_detail_dt
where dt between date_add('{{ execution_date  | cst_ds }}',-60) and '{{ execution_date  | cst_ds }}'
group by  
    network_code --网点code
    ,belong_type 
    ,date_time    
)t1 */
)main left join jms_dim.dim_network_whole_massage net on main.network_code=net.code and main.network_code is not null

distribute by 1;
